package com.ulic.customerManApplication.membermodule.dao.impl;

import java.math.BigDecimal;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;
import org.springframework.stereotype.Component;

import com.ulic.customerManApplication.base.BaseDao;
import com.ulic.customerManApplication.constant.CONSTANT;
import com.ulic.customerManApplication.membermodule.bo.SubMember;
import com.ulic.customerManApplication.membermodule.dao.SubMemberDao;
import com.ulic.customerManApplication.membermodule.vo.SubMemberVO;
import com.ulic.customerManApplication.pager.PagerModel;
import com.ulic.customerManApplication.usermodule.bo.User;
@Component("subMemberDao")
public class SubMemberDaoImpl extends BaseDao implements SubMemberDao {

	Session session = null;
	@Override
	public void deleteSubMember(SubMember subMember) throws Exception {
		session = getHibernateTemplate().getSessionFactory().getCurrentSession();
		session.delete(subMember);
		session.flush();
		
	}

	@Override
	public SubMember getSubMember(int id) {
		session = getHibernateTemplate().getSessionFactory().getCurrentSession();
		Criteria cri = session.createCriteria(SubMember.class);
		List list = cri.add(Restrictions.eq("sub_id", id)).list();
		SubMember subMember = null;
		if(list.size()>0){
			subMember	= (SubMember)list.get(0);
		}
		return subMember;
	}

	@Override
	public PagerModel getSubMembers(int curPage) {
		return null;
	}

	@Override
	public List<SubMember> getSubMembers(User user) {
		session = getHibernateTemplate().getSessionFactory().getCurrentSession();
		Criteria cri = session.createCriteria(SubMember.class);
		List list = cri.createAlias("sub_user", "u").add(Restrictions.eq("u.um_user_id", user.getUm_user_id())).list();
		return list;
	}

	@Override
	public PagerModel getSubMembers(SubMemberVO subMemberVO, int curPage) {
		StringBuilder sb = new StringBuilder();
		sb.append(" from SubMember subMember where subMember.sub_journeyOfLoveActivity.id="+subMemberVO.getJourneyLoveId());
		SubMember subMember = subMemberVO.getSubMember();
		if(subMember != null){
			if(! "No".equals(subMember.getSub_gender())){
				sb.append(" and subMember.sub_gender = '"+subMember.getSub_gender()+"'");
			}
			if(subMember.getSubCompany()!= null ){
				if(! "No".equals(subMember.getSubCompany())){
					sb.append(" and subMember.subCompany = '"+subMember.getSubCompany()+"'");
				}
			}
			if(subMember.getSubCity()!= null ){
				if(! "No".equals(subMember.getSubCity())){
					sb.append(" and subMember.subCity = '"+subMember.getSubCity()+"'");
				}
			}
			if(! "No".equals(subMember.getSub_memberType())){
				sb.append(" and subMember.sub_memberType = '"+subMember.getSub_memberType()+"'");
			}
			if(subMember.getSub_memberName()!=null && subMember.getSub_memberName().length()!=0){
				sb.append(" and subMember.sub_memberName like '%"+subMember.getSub_memberName()+"%'");
			}
			if(subMember.getSub_carType()!=null && subMember.getSub_carType().length()!=0){
				sb.append(" and subMember.sub_carBrand like '%"+subMember.getSub_carType()+"%'");
			}
			if(subMemberVO.getStartbirthday()!=null &&subMemberVO.getStartbirthday().length()!=0){
				sb.append(" and to_date(subMember.sub_birthday,'YYYY-MM-DD  HH24:MI:SS') >= to_date('"+subMemberVO.getStartbirthday()+" 00:00:00"+"','YYYY-MM-DD  HH24:MI:SS') ");
			}
			if(subMemberVO.getEndbirthday()!=null &&subMemberVO.getEndbirthday().length()!=0){
				sb.append(" and to_date(subMember.sub_birthday,'YYYY-MM-DD  HH24:MI:SS') <= to_date('"+subMemberVO.getEndbirthday()+" 23:59:59"+"','YYYY-MM-DD  HH24:MI:SS') ");
			}
			
			if(subMemberVO.getStartcreate()!=null &&subMemberVO.getStartcreate().length()!=0){
				sb.append(" and subMember.sub_createDate>= to_date('"+subMemberVO.getStartcreate()+" 00:00:00"+"','YYYY-MM-DD  HH24:MI:SS') ");
			}
			if(subMemberVO.getEndcreate()!=null &&subMemberVO.getEndcreate().length()!=0){
				sb.append(" and subMember.sub_createDate <= to_date('"+subMemberVO.getEndcreate()+" 23:59:59"+"','YYYY-MM-DD  HH24:MI:SS') ");
			}
			 
		}
		sb.append(" order by subMember.sub_donateStuNum desc");
		return searchPaginated(sb.toString(), curPage, CONSTANT.PageSize);
	}

	@Override
	public void insertSubMember(SubMember subMember) throws Exception {
		session = getHibernateTemplate().getSessionFactory().getCurrentSession();
		session.save(subMember);
		session.flush();
	}

	@Override
	public void insertSubMembers(List<SubMember> subMembers) throws Exception {
		session = getHibernateTemplate().getSessionFactory().getCurrentSession();
		int count = 0;
		for(SubMember subMember:subMembers){
			session.save(subMember);
			count++;
			if(count==15){
				session.flush();
				count = 0;
			}
		}
		
	}

	@Override
	public void updateSubMember(SubMember subMember) throws Exception {
		try{
        	session = getHibernateTemplate().getSessionFactory().getCurrentSession();
        	session.merge(subMember);
        	session.flush();
        }catch (Exception e) {
			// TODO: handle exception
        	e.printStackTrace();
		}
	}

	@Override
	public List<SubMember> getSubMembers(String idCard) {
		session = getHibernateTemplate().getSessionFactory().getCurrentSession();
		Criteria cri = session.createCriteria(SubMember.class);
		List list = cri.add(Restrictions.eq("sub_idCard", idCard)).list();
		return list;
	}

	@Override
	public List<SubMember> getSubMembersByJourId(Integer journeyLoveId) {
		session = getHibernateTemplate().getSessionFactory().getCurrentSession();
		Criteria cri = session.createCriteria(SubMember.class);
		List list = cri.createAlias("sub_journeyOfLoveActivity", "jour").add(Restrictions.eq("jour.id", journeyLoveId)).addOrder(Order.desc("sub_donateStuNum")).list();
		return list;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<SubMember> getSubMemberByPhone(String phone) throws Exception {
		session = getHibernateTemplate().getSessionFactory().getCurrentSession();
		Criteria cri = session.createCriteria(SubMember.class);
		List<SubMember> list = cri.add(Restrictions.eq("sub_cellPhone", phone)).list();
		return list;
	}

	@SuppressWarnings("unchecked")
	@Override
	public SubMember getSubMemberByPhone(String phone, String journeyLoveId)
			throws Exception {
		session = getHibernateTemplate().getSessionFactory().getCurrentSession();
		Criteria cri = session.createCriteria(SubMember.class);
		List<SubMember> list = cri.createAlias("sub_journeyOfLoveActivity", "jour").add(Restrictions.eq("jour.id", Integer.parseInt(journeyLoveId))).add(Restrictions.eq("sub_cellPhone", phone)).list();
		if(list != null && list.size() > 0){
			return list.get(0);
		}
		return null;
	}

	@Override
	public Map<String, Long> getMemberCountByJourId(Integer journeyLoveId,String memberType) {
		session = this.getHibernateTemplate().getSessionFactory().getCurrentSession();
		String sql = "select count(*), t.sub_membertype from T_GYW_SUB_MEMBER t where t.journeylove_id = ? group by t.sub_membertype";
		Query q = session.createSQLQuery(sql);
		q.setInteger(0, journeyLoveId);
		List<Object[]> list = q.list();
		Map<String,Long> map = new HashMap<String,Long>();
		if(list != null && list.size() > 0){
			for(Object[] obj : list){
				map.put((String)obj[1], ((BigDecimal)obj[0]).longValue());
			}
		}
		return map;
	}
	
	

	

}
